package com.hst.capacity.infra.persistence.jpa.driver;

import com.hst.capacity.domain.model.entity.driver.DriverEntity;
import com.hst.capacity.domain.model.entity.driver.DriverProfileEntity;
import com.hst.capacity.domain.model.response.OneToOnePO;
import com.hst.capacity.domain.model.response.fleet.FleetSearchDriverVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.jpa.repository.Modifying;

import java.util.List;


public interface DriverRepository extends JpaRepository<DriverEntity, String>, JpaSpecificationExecutor<DriverEntity> {

    /**
     * 根据司机ID查找司机信息
     * @param idDriver
     * @return
     */
    DriverEntity findByIdDriver(String idDriver);

    /**
     * 根据司机ID查找司机信息
     * @param mobile
     * @return
     */
    DriverEntity findByDriverMobile(String mobile);

    /**
     * 根据车辆查找司机信息
     * @param idFranchisee
     * @return
     */
    @Query(nativeQuery=true,
            value="select * from dm_driver " +
            "where id_franchisee = ?1 and id_vehicle is not null " +
            "and id_driver_profile is not null order by RAND() LIMIT 1")
    //@Query("select t from DriverEntity t where t.idFranchisee = ?1 order by RAND() limit 1")
    DriverEntity findDriverByIdFranchiseeWithRand(String idFranchisee);

    /**
     * 根据车辆查找司机信息
     * @param vehicleEntityId
     * @return
     */
    DriverEntity findByIdVehicle(String vehicleEntityId);

    /**
     * 司机绑定车辆
     * @param idDriver
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query("UPDATE DriverEntity d SET d.idVehicle = ?1 WHERE d.idDriver = ?2")
    int driverBandVehicle(String idVehicle, String idDriver);

    /**
     * 更新司机信息表ID
     * @param idDriver
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query("UPDATE DriverEntity d SET d.idDriverProfile = ?1 WHERE d.idDriver = ?2")
    int updateProfileId(String idDriverProfile, String idDriver);

    /**
     * 解绑司机车辆
     * @param idDriver
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query("UPDATE DriverEntity d SET d.idVehicle = null WHERE d.idDriver = ?1")
    int unBandVehicle(String idDriver);

    /**
     * 更新司机状态
     * @param idDriver
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query("UPDATE DriverEntity d SET d.status = ?1 WHERE d.idDriver = ?2")
    int driverUpdateStatus(Integer status, String idDriver);

    /**
     * 更新司机上线任务状态
     * @param idDriver
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query("UPDATE DriverEntity d SET d.operationStatus = ?1 WHERE d.idDriver = ?2")
    int driverUpdateOperationStatus(Integer operationStatus, String idDriver);

    /**
     * 查询被车队绑定的司机信息
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.fleet.FleetSearchDriverVO(d,dp,v,vl) " +
            "from DriverEntity d " +
            "left join DriverProfileEntity dp on d.idDriverProfile = dp.idDriverProfile " +
            "left join VehicleEntity v on d.idVehicle = v.idVehicle " +
            "left join VehicleLicenseEntity vl on v.idVehicle = vl.idVehicle " +
            "where d.idFleet=?1")
    Page<FleetSearchDriverVO> findByIdFleetBand(String idFleet, PageRequest pageRequest);

    /**
     * 查询司机信息通过电话号码
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.fleet.FleetSearchDriverVO(d,dp,v,vl) " +
            "from DriverEntity d " +
            "left join DriverProfileEntity dp on d.idDriverProfile = dp.idDriverProfile " +
            "left join VehicleEntity v on d.idVehicle = v.idVehicle " +
            "left join VehicleLicenseEntity vl on v.idVehicle = vl.idVehicle " +
            "where d.idFleet is NULL and d.driverMobile like CONCAT('%',?1,'%') and (d.idFranchisee = ?2 or d.idFranchisee is NULL) " +
            "and (d.idAgent = ?3 or d.idAgent is NULL)")
    Page<FleetSearchDriverVO> searchDriverMobile(String mobile, String idFranchisee, String idAgent, PageRequest pageRequest);

    /**
     * 查询未被车队绑定的司机
     * @param idFranchisee
     * @param pageRequest
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.fleet.FleetSearchDriverVO(d,dp,v,vl) " +
            "from DriverEntity d " +
            "left join DriverProfileEntity dp on d.idDriverProfile = dp.idDriverProfile " +
            "left join VehicleEntity v on d.idVehicle = v.idVehicle " +
            "left join VehicleLicenseEntity vl on v.idVehicle = vl.idVehicle " +
            "where d.idFleet is NULL and (d.idFranchisee = ?1 or d.idFranchisee is NULL) and (d.idAgent = ?2 or d.idAgent is NULL)")
    Page<FleetSearchDriverVO> findByIdFleetUnBand(String idFranchisee, String idAgent, PageRequest pageRequest);

    @Query("select new com.hst.capacity.domain.model.response.OneToOnePO(d,dp) from DriverEntity d join DriverProfileEntity dp on d.idDriverProfile=dp.idDriverProfile where d.idAgent=?1")
    List<OneToOnePO<DriverEntity, DriverProfileEntity>> queryByIdAgent(String idAgent);

    @Query("select count(idDriver) from DriverEntity where idAgent=?1")
    int agentDriverCount(String idAgent);

    @Query("select count(idDriver) from DriverEntity where idFleet=?1")
    int fleetDriverCount(String idFleet);

}
